package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.entity.Idea;
import com.entity.User;
import com.util.DBHelp;
import com.util.PageHelp;
import com.util.RowMapper;

public class IdeaDao {
	private DBHelp<Idea> db=new DBHelp<Idea>();
	public Long getCount() {
		String sql="select count(id) from t_idea";
		return (Long) db.executeQueryForFunction(sql);
	}

	private class IdeaRowMapper implements RowMapper<Idea>{

		public Idea mapRow(ResultSet rs) throws SQLException {
			Idea i=new Idea();
			i.setId(rs.getString("id"));
			i.setMessage(rs.getString("message"));
			i.setCreateTime(rs.getString("createtime"));
			i.setIdeaName(rs.getString("ideaname"));
			
			User u=new User();
			i.setUserid(rs.getString("userid"));
			u.setId(rs.getString("userid"));
			u.setImage(rs.getString("image"));
			u.setUserName(rs.getString("username"));
			i.setUser(u);
			i.setProjectid(rs.getString("projectid"));
			return i;
		}
		
	}

	public PageHelp<Idea> findByPage(PageHelp<Idea> page) {
		String sql="SELECT ti.id,ti.ideaname,ti.message,ti.createtime,ti.userid,ti.projectid, tu.id,tu.username,tu.image,tp.projectname FROM t_idea AS ti LEFT JOIN t_user AS tu ON ti.userid=tu.id LEFT JOIN t_project AS tp ON ti.projectid=tp.id ORDER BY ti.createtime DESC limit ?,?";
		List<Idea> list=db.executeQueryForList(sql, new IdeaRowMapper(),page.getOffSet(),page.getPageSize());
		page.setResult(list);
		return page;
	}

	public void saveIdea(Idea i) {
		String sql="INSERT INTO t_idea(id,ideaname,userid,projectid,createtime,message) VALUES(?,?,?,?,?,?)";
		db.executeSQL(sql,i.getId(),i.getIdeaName(),i.getUserid(),i.getProjectid(),i.getCreateTime(),i.getMessage());
	}

	public Idea findIdeaById(String ideaid) {
		String sql=" SELECT ti.id,ti.ideaname,ti.message,ti.createtime,ti.userid,ti.projectid,tu.username,tu.image FROM t_idea AS ti LEFT JOIN t_user AS tu ON ti.userid=tu.id WHERE ti.id=?";
		return db.executeQueryForObject(sql, new IdeaRowMapper(),ideaid);
	}

	public boolean updateIdeaById(Idea i) {
		String sql="update t_idea set ideaname=? ,message=? where id=?";
		return db.executeSQL(sql, i.getIdeaName(),i.getMessage(),i.getId());
		
	}

	public boolean delIdeaById(String ideaid) {
		String sql="delete from t_idea where id=?";
		return db.executeSQL(sql, ideaid);
	}
}
